<?php
use Migrations\AbstractMigration;

class POCOR6994 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function up()
    {
        // Backup table
        $this->execute('CREATE TABLE `zz_6994_report_queries` LIKE `report_queries`');
        $this->execute('INSERT INTO `zz_6994_report_queries` SELECT * FROM `report_queries`');


        // Make Class and Staff columns Nullable
        $this->execute('ALTER TABLE `report_student_assessment_summary` CHANGE report_student_assessment_summary.institution_classes_id report_student_assessment_summary.institution_classes_id int(11) DEFAULT NULL');
        $this->execute('ALTER TABLE `report_student_assessment_summary` CHANGE report_student_assessment_summary.institution_classes_name report_student_assessment_summary.institution_classes_name varchar(100) DEFAULT NULL');
        $this->execute('ALTER TABLE `report_student_assessment_summary` CHANGE report_student_assessment_summary.homeroom_teacher_id report_student_assessment_summary.homeroom_teacher_id int(11) DEFAULT NULL');
        $this->execute('ALTER TABLE `report_student_assessment_summary` CHANGE report_student_assessment_summary.homeroom_teacher_name report_student_assessment_summary.homeroom_teacher_name varchar(250) DEFAULT NULL');

        // Replace INSERT statement
        $this->execute('DELETE FROM `report_queries` WHERE `report_queries`.`name` LIKE "report_student_assessment_summary_insert"');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("report_student_assessment_summary_insert", "INSERT INTO report_student_assessment_summary SELECT academic_periods.id academic_period_id ,academic_periods.code academic_period_code ,academic_periods.name academic_period_name ,areas.id area_id ,areas.code area_code ,areas.name area_name ,institutions.id institution_id ,institutions.code institution_code ,institutions.name institution_name ,education_grades.id education_grade_id ,education_grades.code education_grade_code ,education_grades.name education_grade_name ,institution_classes.id institution_class_id ,institution_classes.name institution_class_name ,homeroom_teacher.id homeroom_teacher_id ,CONCAT(homeroom_teacher.first_name,homeroom_teacher.last_name) homeroom_teacher_name ,education_subjects.id education_subject_id ,education_subjects.code education_subject_code ,education_subjects.name education_subject_name ,assessment_items.weight subject_weight ,assessments.id assessment_id ,assessments.code assessment_code ,assessments.name assessment_name ,assessment_periods.id assessment_period_id ,assessment_periods.code assessment_period_code ,assessment_periods.name assessment_period_name ,assessment_periods.academic_term ,assessment_periods.weight period_weight ,students.id student_id ,CONCAT(students.first_name,students.last_name) student_name ,assessment_item_results.marks latest_mark ,get_total_student_mark.total_mark ,get_avg_student_mark.average_mark ,get_instituion_average_mark.instituion_average_mark ,get_area_average_mark.area_average_mark ,NOW() created FROM assessment_item_results INNER JOIN institutions ON institutions.id = assessment_item_results.institution_id INNER JOIN areas ON areas.id = institutions.area_id INNER JOIN education_grades ON education_grades.id = assessment_item_results.education_grade_id LEFT JOIN institution_classes ON institution_classes.id = assessment_item_results.institution_classes_id AND institution_classes.institution_id = institutions.id INNER JOIN education_subjects ON education_subjects.id = assessment_item_results.education_subject_id INNER JOIN assessments ON assessments.id = assessment_item_results.assessment_id AND assessments.academic_period_id = assessment_item_results.academic_period_id AND assessments.education_grade_id = assessment_item_results.education_grade_id INNER JOIN assessment_items ON assessment_items.assessment_id = assessments.id AND assessment_items.education_subject_id = assessment_item_results.education_subject_id INNER JOIN assessment_periods ON assessment_periods.id = assessment_item_results.assessment_period_id INNER JOIN security_users students ON students.id = assessment_item_results.student_id LEFT JOIN security_users homeroom_teacher ON institution_classes.staff_id = homeroom_teacher.id INNER JOIN( SELECT assessment_item_results.student_id ,assessment_item_results.assessment_period_id ,assessment_item_results.education_subject_id ,MAX(assessment_item_results.created) latest_created FROM assessment_item_results GROUP BY assessment_item_results.student_id ,assessment_item_results.assessment_period_id ,assessment_item_results.education_subject_id) latest_data ON latest_data.student_id = assessment_item_results.student_id AND latest_data.assessment_period_id = assessment_item_results.assessment_period_id AND latest_data.education_subject_id = assessment_item_results.education_subject_id AND latest_data.latest_created = assessment_item_results.created INNER JOIN academic_periods ON academic_periods.id = assessment_item_results.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.institution_class_id ,institution_subject_students.education_grade_id ,institution_subject_students.student_id ,institution_subject_students.education_subject_id ,ROUND(AVG(institution_subject_students.total_mark),2) total_mark FROM institution_subject_students GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.institution_class_id ,institution_subject_students.education_grade_id ,institution_subject_students.student_id ,institution_subject_students.education_subject_id ) get_total_student_mark ON get_total_student_mark.academic_period_id = assessment_item_results.academic_period_id AND get_total_student_mark.institution_id = assessment_item_results.institution_id AND get_total_student_mark.institution_class_id = assessment_item_results.institution_classes_id AND get_total_student_mark.education_grade_id = assessment_item_results.education_grade_id AND get_total_student_mark.student_id = assessment_item_results.student_id AND get_total_student_mark.education_subject_id = assessment_item_results.education_subject_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.institution_class_id ,institution_subject_students.education_grade_id ,institution_subject_students.student_id ,ROUND(AVG(institution_subject_students.total_mark),2) average_mark FROM institution_subject_students GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.institution_class_id ,institution_subject_students.education_grade_id ,institution_subject_students.student_id ) get_avg_student_mark ON get_avg_student_mark.academic_period_id = assessment_item_results.academic_period_id AND get_avg_student_mark.institution_id = assessment_item_results.institution_id AND get_avg_student_mark.institution_class_id = assessment_item_results.institution_classes_id AND get_avg_student_mark.education_grade_id = assessment_item_results.education_grade_id AND get_avg_student_mark.student_id = assessment_item_results.student_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,ROUND(AVG(institution_subject_students.total_mark),2) instituion_average_mark FROM institution_subject_students GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ) get_instituion_average_mark ON get_instituion_average_mark.academic_period_id = assessment_item_results.academic_period_id AND get_instituion_average_mark.institution_id = assessment_item_results.institution_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institutions.area_id ,ROUND(AVG(institution_subject_students.total_mark),2) area_average_mark FROM institution_subject_students INNER JOIN institutions ON institutions.id = institution_subject_students.institution_id GROUP BY institution_subject_students.academic_period_id ,institutions.area_id ) get_area_average_mark ON get_area_average_mark.academic_period_id = assessment_item_results.academic_period_id AND get_area_average_mark.area_id = areas.id", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP())');
         
    }
         
    // rollback
    public function down()
    {
        // Restore table
        $this->execute('DROP TABLE IF EXISTS `report_queries`');
        $this->execute('RENAME TABLE `zz_6994_report_queries` TO `report_queries`');

    }
}